/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.CarCancelDTO;
import east2west.interfaces.CarCancelInterface;
import east2west.util.DatabaseUtil;
import east2west.util.DateTimeUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author anhtuan
 */
public class CarCancelDAO implements CarCancelInterface {

    Connection con = null;
    CarCancelDTO carCancel = null;
    CallableStatement call = null;
    ResultSet rs = null;
    private final String SQL_READ_BY_ALL = "select * from V_CarCancel";
    private final String SQL_READ_BY_ID = "{call sp_Call_View_CarCancel_FindId (?)}";
    private final String SQL_READ_BY_CARORDERDETAILID = "{call sp_Call_View_CarCancel_FindCarOrderDetailId (?)}";
    private final String SQL_READ_BY_CARDATECANCEL = "{call sp_Call_View_CarCancel_FindDateCancel (?)}";
    private final String SQL_READ_BY_DISCOUNT = "{call sp_Call_View_CarCancel_FindDiscount (?)}";
    private final String SQL_CREATE = "{call sp_Insert_CarCancel (?,?,?)}";
    private final String SQL_UPDATE = "{call sp_Update_CarCancel (?,?,?,?)}";
    private final String SQL_DELETE = "{call sp_Delete_CarCancel (?)}";

    public ArrayList<CarCancelDTO> readByAll() {
        ArrayList<CarCancelDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_ALL);
            rs = call.executeQuery();
            list = new ArrayList<CarCancelDTO>();
            while (rs.next()) {
                carCancel = new CarCancelDTO();
                carCancel.setCarCancelId(rs.getInt(1));
                carCancel.setCarOderDetailId(rs.getInt(2));
                carCancel.setDateCancel(DateTimeUtil.convert2String(rs.getDate(3), "MM/dd/yyyy"));
                carCancel.setDiscount(rs.getFloat(4));
                list.add(carCancel);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public CarCancelDTO findByid(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_ID);
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                carCancel = new CarCancelDTO();
                carCancel.setCarCancelId(rs.getInt(1));
                carCancel.setCarOderDetailId(rs.getInt(2));
                carCancel.setDateCancel(DateTimeUtil.convert2String(rs.getDate(3), "dd/MM/yyyy"));
                carCancel.setDiscount(rs.getFloat(4));
            }
            rs.close();
            call.close();
            return carCancel;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public CarCancelDTO findByCarOrderDetailid(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_CarCancel where CarOderDetailId = ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                carCancel = new CarCancelDTO();
                carCancel.setCarCancelId(rs.getInt(1));
                carCancel.setCarOderDetailId(rs.getInt(2));
                carCancel.setDateCancel(DateTimeUtil.convert2String(rs.getDate(3), "MM/dd/yyyy"));
                carCancel.setDiscount(rs.getFloat(4));
            }
            rs.close();
            call.close();
            return carCancel;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarCancelDTO> findByDateCancel(String date) {
        ArrayList<CarCancelDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_CARDATECANCEL);
            call.setDate(1, DateTimeUtil.convert2Date(date, "dd/MM/yyyy"));
            rs = call.executeQuery();
            list = new ArrayList<CarCancelDTO>();
            while (rs.next()) {
                carCancel = new CarCancelDTO();
                carCancel.setCarCancelId(rs.getInt(1));
                carCancel.setCarOderDetailId(rs.getInt(2));
                carCancel.setDateCancel(DateTimeUtil.convert2String(rs.getDate(3), "dd/MM/yyyy"));
                carCancel.setDiscount(rs.getFloat(4));
                list.add(carCancel);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarCancelDTO> findByDiscount(float discount) {
        ArrayList<CarCancelDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_DISCOUNT);
            call.setFloat(1, discount);
            rs = call.executeQuery();
            list = new ArrayList<CarCancelDTO>();
            while (rs.next()) {
                carCancel = new CarCancelDTO();
                carCancel.setCarCancelId(rs.getInt(1));
                carCancel.setCarOderDetailId(rs.getInt(2));
                carCancel.setDateCancel(DateTimeUtil.convert2String(rs.getDate(3), "dd/MM/yyyy"));
                carCancel.setDiscount(rs.getFloat(4));
                list.add(carCancel);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public boolean create(CarCancelDTO dto) {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall(SQL_CREATE);
            call.setInt(1, dto.getCarOderDetailId());
            call.setString(2, dto.getDateCancel());
            call.setFloat(3, dto.getDiscount());
            call.executeUpdate();
            con.commit();

            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
            }
        }
    }

    public boolean update(CarCancelDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall(SQL_UPDATE);
            call.setInt(1, dto.getCarCancelId());
            call.setInt(2, dto.getCarOderDetailId());
            call.setDate(3, DateTimeUtil.convert2Date(dto.getDateCancel(), "dd/MM/yyyy"));
            call.setFloat(4, dto.getDiscount());
            call.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(CarCancelDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall(SQL_DELETE);
            call.setInt(1, id);
            call.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
            }
        }
    }
}
